
package com.teamcalendar.client;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter 
{
	public static final String KEY_ID_MEMBERS = "_id";
	public static final String KEY_TEAMID = "teamId";
	public static final String KEY_MEMBERID = "memberId";
	public static final String KEY_FIRSTNAME = "firstName";
	public static final String KEY_LASTNAME = "lastName";
	public static final String KEY_ADDRESS = "address";
	public static final String KEY_LOCATION = "location";
	public static final String KEY_ZIPCODE = "zipCode";
	public static final String KEY_PHONENUMBER = "phoneNumber";
	public static final String KEY_EMAIL = "email";
	public static final String KEY_ROLE = "role";
	public static final String KEY_MEMBER_IMAGE="image";

	public static final String KEY_ID_MESSAGES = "_id";
	public static final String KEY_MESSAGE = "message";
	public static final String KEY_READ = "read";
	public static final String KEY_SENDERID = "senderId";
	public static final String KEY_MESSAGE_ID = "messageId";
	
	public static final String KEY_ID_TEAMS = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_IMAGE = "image";
	public static final String KEY_TEAMIDS = "teamIds";
	public static final String KEY_CALENDAR = "calendar";

	
	public static final String KEY_ID_EVENT="_id";
	public static final String KEY_ID_STRING_EVENT="event_id";
	public static final String KEY_DATE_EVENT="date";
	public static final String KEY_EVENT_DATAILS_EVENT="event";
	
	public static final String KEY_IMAGE_NAME="image_name";
	
	private static final String TAG = "DbAdapter";

	private static final String DATABASE_NAME = "teamnew.db";
	public static final String DATABASE_TABLE_TEAMS = "teams";
	//Nya tabeller
	public static final String DATABASE_TABLE_MYTEAMMEMBERS = "myteammembers";
	public static final String DATABASE_TABLE_CURRENTTEAMMEMBERS = "currentteammembers";
	public static final String DATABASE_TABLE_MESSAGES = "messages";
	public static final String DATABASE_TABLE_MYEVENTS="myevents";
	public static final String DATABASE_TABLE_CURRENTTEAMEVENTS="currentteamevents";
	public static final String DATABASE_TABLE_IMAGES="images";

	private static final int DATABASE_VERSION = 1;

	private static final String CREATE_DATABASE_TABLE_TEAMS =
		"create table if not exists " + DATABASE_TABLE_TEAMS + 
		" (_id integer primary key autoincrement, " +
		"name text not null, " +
		"teamIds text not null, " +
		"image text" +
		");"; 


	private static final String CREATE_DATABASE_TABLE_CURRENTTEAMMEMBERS =
		"create table if not exists " + DATABASE_TABLE_CURRENTTEAMMEMBERS + 
		" (_id integer primary key autoincrement, " +
		"teamId text not null, " +
		"firstName text not null, " +
		"lastName text not null, " +
		"address text not null, " +
		"location text not null, " +
		"zipCode text not null, " +
		"phoneNumber text not null, " +
		"email text not null, " +
		"image text, " +
		"role text not null " +
		");";
		

	private static final String CREATE_DATABASE_TABLE_MYTEAMMEMBERS =
		"create table if not exists " + DATABASE_TABLE_MYTEAMMEMBERS + 
		" (_id integer primary key autoincrement, " +
		"memberId text not null, " +
		"firstName text not null, " +
		"lastName text not null, " +
		"address text not null, " +
		"location text not null, " +
		"zipCode text not null, " +
		"phoneNumber text not null, " +
		"email text not null, " +
		"image text, " +
		"role text not null " +
		");";
	
	private static final String CREATE_DATABASE_TABLE_MESSAGES =
		"create table if not exists " + DATABASE_TABLE_MESSAGES + 
		" (_id integer primary key autoincrement, " +
		"senderId text not null, " +
		"message text not null, " +
		"messageId text not null," +
		"read integer null " +
		");";
	
	private static final String CREATE_DATABASE_TABLE_MYEVENTS =
		"create table if not exists " + DATABASE_TABLE_MYEVENTS + 
		" (_id integer primary key autoincrement, " +
		"event_id text not null,"+
		"date long not null, " +
		"event text not null " +
		");";
	
	private static final String CREATE_DATABASE_TABLE_CURRENTTEAMEVENTS =
		"create table if not exists " + DATABASE_TABLE_CURRENTTEAMEVENTS + 
		" (_id integer primary key autoincrement, " +
		"event_id text not null,"+
		"date long not null, " +
		"event text not null " +
		");";
	
	private static final String CREATE_DATABASE_TABLE_IMAGES =
		"create table if not exists " + DATABASE_TABLE_IMAGES +
		" (_id integer primary key autoincrement, " + 
		"image_name text not null," +
		"image text not null" +
		");";
	private final Context context; 

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DBAdapter(Context ctx) 
	{
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper 
	{
		DatabaseHelper(Context context) 
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{
			db.execSQL(CREATE_DATABASE_TABLE_TEAMS);
			db.execSQL(CREATE_DATABASE_TABLE_CURRENTTEAMMEMBERS);
			db.execSQL(CREATE_DATABASE_TABLE_MYTEAMMEMBERS);
			db.execSQL(CREATE_DATABASE_TABLE_MESSAGES);
			db.execSQL(CREATE_DATABASE_TABLE_MYEVENTS);
			db.execSQL(CREATE_DATABASE_TABLE_CURRENTTEAMEVENTS);
			db.execSQL(CREATE_DATABASE_TABLE_IMAGES);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, 
				int newVersion) 
		{
			Log.w(TAG, "Upgrading database from version " + oldVersion 
					+ " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_TEAMS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_CURRENTTEAMMEMBERS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MYTEAMMEMBERS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MESSAGES);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MYEVENTS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_CURRENTTEAMEVENTS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_IMAGES);
			onCreate(db);
		}
	}
	

	//---opens the database---
	public DBAdapter open() throws SQLException 
	{
		db = DBHelper.getWritableDatabase();
		return this;
	}

	//---closes the database---    
	public void close() 
	{
		DBHelper.close();
	}

	//---insert a member into the database---
	public long insertMyTeamMember(String memberId, String firstName, String lastName, 
			String address, String location, String zipCode, 
			String phoneNumber, String email, String role,String image) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_MEMBERID, memberId); 
		initialValues.put(KEY_FIRSTNAME, firstName);
		initialValues.put(KEY_LASTNAME, lastName);
		initialValues.put(KEY_ADDRESS, address);
		initialValues.put(KEY_LOCATION, location);
		initialValues.put(KEY_ZIPCODE, zipCode);
		initialValues.put(KEY_PHONENUMBER, phoneNumber);
		initialValues.put(KEY_EMAIL, email);
		initialValues.put(KEY_ROLE, role);
		initialValues.put(KEY_MEMBER_IMAGE, image);
		return db.insert(DATABASE_TABLE_MYTEAMMEMBERS, null, initialValues);
	}

	//---deletes a particular member---
	public boolean deleteMyTeamMember(long rowId) 
	{
		return db.delete(DATABASE_TABLE_MYTEAMMEMBERS, KEY_ID_MEMBERS + 
				"=" + rowId, null) > 0;
	}

	//---retrieves all the member---
	public Cursor getAllMyTeamMembers() 
	{
		return db.query(DATABASE_TABLE_MYTEAMMEMBERS, new String[] {
				KEY_ID_MEMBERS, 
				KEY_MEMBERID,
				KEY_FIRSTNAME,
				KEY_LASTNAME,
				KEY_ADDRESS,
				KEY_LOCATION,
				KEY_ZIPCODE,
				KEY_PHONENUMBER,
				KEY_EMAIL,
				KEY_ROLE,
				KEY_MEMBER_IMAGE
		}, 
		null, 
		null, 
		null, 
		null, 
		null);
	}
	
	//---insert a message into the database---
	public long insertMessage(String senderId, String message, String messageId, Integer read) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SENDERID, senderId);
		initialValues.put(KEY_MESSAGE, message);
		initialValues.put(KEY_MESSAGE_ID, messageId);
		initialValues.put(KEY_READ, read);

		return db.insert(DATABASE_TABLE_MESSAGES, null, initialValues);
	}

	//---deletes a particular message---
	public boolean deleteMessage(long rowId) 
	{
		return db.delete(DATABASE_TABLE_MESSAGES, KEY_ID_MESSAGES + 
				"=" + rowId, null) > 0;
	}
	
	//---retrieves a particular message---
	public Cursor getMessage(long rowId) throws SQLException 
	{
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_MESSAGES, new String[] {
					KEY_ID_MESSAGES,
					KEY_SENDERID,
					KEY_MESSAGE,
					KEY_MESSAGE_ID,
					KEY_READ
			}, 
			KEY_ID_MESSAGES + "=" + rowId, 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	//---retrieves all the messages---
	public Cursor getAllMessages() 
	{
		return db.query(DATABASE_TABLE_MESSAGES, new String[] {
				KEY_ID_MESSAGES,
				KEY_SENDERID,
				KEY_MESSAGE,
				KEY_MESSAGE_ID,
				KEY_READ
		}, 
		null, 
		null, 
		null, 
		null, 
		KEY_ID_MESSAGES + " ASC");
	}
	
	//---updates a message---
	public boolean updateMessage(long rowId, String senderId, String message, String messageId, 
			Integer read) 
	{
		ContentValues args = new ContentValues();
		args.put(KEY_SENDERID, senderId);
		args.put(KEY_MESSAGE, message);
		args.put(KEY_MESSAGE_ID, messageId);
		args.put(KEY_READ, read);
		return db.update(DATABASE_TABLE_MESSAGES, args, 
				KEY_ID_MESSAGES + "=" + rowId, null) > 0;

	}



	public Cursor getMyTeamMember(String memberId) throws SQLException 
	{
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_MYTEAMMEMBERS, new String[] {
					KEY_ID_MEMBERS, 
					KEY_MEMBERID,
					KEY_FIRSTNAME,
					KEY_LASTNAME,
					KEY_ADDRESS,
					KEY_LOCATION,
					KEY_ZIPCODE,
					KEY_PHONENUMBER,
					KEY_EMAIL,
					KEY_ROLE,
					KEY_MEMBER_IMAGE
			}, 
			KEY_MEMBERID + "=" + "'" + memberId + "'",  
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	//---updates a member---
	public boolean updateMyTeamMember(String memberId, String firstName, String lastName, 
			String address, String location, String zipCode, 
			String phoneNumber, String email, String role) 
	{
		ContentValues args = new ContentValues();
		args.put(KEY_FIRSTNAME, firstName);
		args.put(KEY_LASTNAME, lastName);
		args.put(KEY_ADDRESS, address);
		args.put(KEY_LOCATION, location);
		args.put(KEY_ZIPCODE, zipCode);
		args.put(KEY_PHONENUMBER, phoneNumber);
		args.put(KEY_EMAIL, email);
		args.put(KEY_ROLE, role);
		return db.update(DATABASE_TABLE_MYTEAMMEMBERS, args, 
				KEY_MEMBERID + "=" + "'" + memberId + "'", null) > 0;

	}

	//---insert a member into the database---
	public long insertCurrentTeamMember(String teamId, String firstName, String lastName, 
			String address, String location, String zipCode, 
			String phoneNumber, String email, String role,String image) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TEAMID, teamId);
		initialValues.put(KEY_FIRSTNAME, firstName);
		initialValues.put(KEY_LASTNAME, lastName);
		initialValues.put(KEY_ADDRESS, address);
		initialValues.put(KEY_LOCATION, location);
		initialValues.put(KEY_ZIPCODE, zipCode);
		initialValues.put(KEY_PHONENUMBER, phoneNumber);
		initialValues.put(KEY_EMAIL, email);
		initialValues.put(KEY_ROLE, role);
		initialValues.put(KEY_MEMBER_IMAGE, image);
		return db.insert(DATABASE_TABLE_CURRENTTEAMMEMBERS, null, initialValues);
	}

	//---deletes a particular member---
	public boolean deleteCurrentTeamMember(long rowId) 
	{
		return db.delete(DATABASE_TABLE_CURRENTTEAMMEMBERS, KEY_ID_MEMBERS + 
				"=" + rowId, null) > 0;
	}
	
	

	//---retrieves all the member---
	public Cursor getAllCurrentTeamMembers() 
	{
		return db.query(DATABASE_TABLE_CURRENTTEAMMEMBERS, new String[] {
				KEY_ID_MEMBERS, 
				KEY_TEAMID,
				KEY_FIRSTNAME,
				KEY_LASTNAME,
				KEY_ADDRESS,
				KEY_LOCATION,
				KEY_ZIPCODE,
				KEY_PHONENUMBER,
				KEY_EMAIL,
				KEY_ROLE,
				KEY_MEMBER_IMAGE
		}, 
		null, 
		null, 
		null, 
		null, 
		null);
	}




	//---retrieves a particular member---
	public Cursor getCurrentTeamMember(long rowId) throws SQLException 
	{
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_CURRENTTEAMMEMBERS, new String[] {
					KEY_ID_MEMBERS, 
					KEY_TEAMID,
					KEY_FIRSTNAME,
					KEY_LASTNAME,
					KEY_ADDRESS,
					KEY_LOCATION,
					KEY_ZIPCODE,
					KEY_PHONENUMBER,
					KEY_EMAIL,
					KEY_ROLE,
					KEY_MEMBER_IMAGE
			}, 
			KEY_ID_MEMBERS + "=" + rowId, 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getMyTeamMember(long rowId) throws SQLException 
	{
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_MYTEAMMEMBERS, new String[] {
					KEY_ID_MEMBERS, 
					KEY_MEMBERID,
					KEY_FIRSTNAME,
					KEY_LASTNAME,
					KEY_ADDRESS,
					KEY_LOCATION,
					KEY_ZIPCODE,
					KEY_PHONENUMBER,
					KEY_EMAIL,
					KEY_ROLE,
					KEY_MEMBER_IMAGE
			}, 
			KEY_ID_MEMBERS + "=" + rowId, 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	//---updates a member---
	public boolean updateCurrentTeamMember(long rowId, String teamId, String firstName, String lastName, 
			String address, String location, String zipCode, String phoneNumber, String email, String role) 
	{
		ContentValues args = new ContentValues();
		args.put(KEY_TEAMID, teamId);
		args.put(KEY_FIRSTNAME, firstName);
		args.put(KEY_LASTNAME, lastName);
		args.put(KEY_ADDRESS, address);
		args.put(KEY_LOCATION, location);
		args.put(KEY_ZIPCODE, zipCode);
		args.put(KEY_PHONENUMBER, phoneNumber);
		args.put(KEY_EMAIL, email);
		args.put(KEY_ROLE, role);
		return db.update(DATABASE_TABLE_CURRENTTEAMMEMBERS, args, 
				KEY_ID_MEMBERS + "=" + rowId, null) > 0;

	}

	//---insert a teams into the database---
	public long insertTeam(String name, String teamIds,String image) 
	{
		ContentValues initValues = new ContentValues();
		initValues.put(KEY_NAME, name);

		initValues.put(KEY_TEAMIDS, teamIds);
		initValues.put(KEY_IMAGE, image);

		return db.insert(DATABASE_TABLE_TEAMS, null, initValues);
	}

	//---deletes a particular team---
	public boolean deleteTeam(long rowId) 
	{
		return db.delete(DATABASE_TABLE_TEAMS, KEY_ID_TEAMS + 
				"=" + rowId, null) > 0;
	}

	//---retrieves all the team---
	public Cursor getAllTeams() 
	{
		return db.query(DATABASE_TABLE_TEAMS, new String[] {
				KEY_ID_TEAMS, 
				KEY_NAME,
				KEY_TEAMIDS,
				KEY_IMAGE,
		}, 
		null, 
		null, 
		null, 
		null, 
		null);
	}

	//---retrieves a particular team---
	public Cursor getTeam(long rowId) throws SQLException 
	{
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_TEAMS, new String[] {
					KEY_ID_TEAMS, 
					KEY_NAME,
					KEY_TEAMIDS,
					KEY_IMAGE,
			}, 
			KEY_ID_TEAMS + "=" + rowId, 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getMyEvents()
	{
			return db.query(DATABASE_TABLE_MYEVENTS,
					null, null, null, null, null, null);
	}
	
	public Cursor getMyevent(String eventId){
		return db.query(DATABASE_TABLE_MYEVENTS,
				new String[]{KEY_EVENT_DATAILS_EVENT,
				KEY_DATE_EVENT
				},
				KEY_ID_STRING_EVENT + "=" + "'" + eventId + "'",
				null, null, null, null);
		
	}
	public Cursor getCurrentEvent(String eventId)
	{
			return db.query(DATABASE_TABLE_MYEVENTS,
					new String[] {KEY_EVENT_DATAILS_EVENT}, 
					KEY_ID_STRING_EVENT + "=" + "'" +eventId + "'", 
					null, null, null, null);
	}
	
	public void updateEvent(String eventId,String eventDetails,long eventdate){
		ContentValues args = new ContentValues();
		args.put(KEY_EVENT_DATAILS_EVENT, eventDetails);
		args.put(KEY_DATE_EVENT,eventdate);
		db.update(DATABASE_TABLE_MYEVENTS, args, 
				   KEY_ID_STRING_EVENT + " = " + 
				   	"'" +eventId + "'", null);
		
	}

	//---updates a team---
	public boolean updateTeam(long rowId, String name, String teamIds) 
	{
		ContentValues argmts = new ContentValues();
		argmts.put(KEY_NAME, name);
		argmts.put(KEY_TEAMIDS, teamIds);
		return db.update(DATABASE_TABLE_TEAMS, argmts, 
				KEY_ID_TEAMS + "=" + rowId, null) > 0;
	}



	public Cursor getMyTeam(String rowId) {
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_TEAMS, new String[] {
					KEY_ID_TEAMS,
					KEY_NAME,
					KEY_IMAGE
			}, 
			KEY_TEAMIDS + "=" + "'"+rowId+"'", 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		return mCursor;    	
	}


	
	public Cursor getProfileInfo(String memberId){
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_MYTEAMMEMBERS, new String[] {
					KEY_FIRSTNAME,
					KEY_LASTNAME,
					KEY_ADDRESS,
					KEY_LOCATION,
					KEY_ZIPCODE,
					KEY_PHONENUMBER,
					KEY_EMAIL,
					KEY_ROLE

			}, 
			KEY_MEMBERID + "=" + "'" + memberId + "'", 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		return mCursor;  
	}


	public void deleteTable(String table) {
		db.delete(table, null, null);    	
	}

	public String getTeamIds(long rowId){
		String teamIds=null;
		Cursor mCursor =
			db.query(true, DATABASE_TABLE_TEAMS, new String[] {
					KEY_TEAMIDS
			}, 
			KEY_ID_TEAMS + "=" + rowId, 
			null,
			null, 
			null, 
			null, 
			null);
		if (mCursor != null) {
			mCursor.moveToFirst();
			teamIds=mCursor.getString(0);
			mCursor.close();
		}
		return teamIds;

	}
	
	public Long insertMyEvents(String eventId,long date, String eventDetails) {
		ContentValues initValues = new ContentValues();
		initValues.put(KEY_ID_STRING_EVENT,eventId);
		initValues.put(KEY_DATE_EVENT, date);
		initValues.put(KEY_EVENT_DATAILS_EVENT, eventDetails);
		return db.insert(DATABASE_TABLE_MYEVENTS, null, initValues);

	}
	
	public Long insertCurrentTeamEvents(String eventId,long date, String eventDetails) {
		ContentValues initValues = new ContentValues();
		initValues.put(KEY_ID_STRING_EVENT,eventId);
		initValues.put(KEY_DATE_EVENT, date);
		initValues.put(KEY_EVENT_DATAILS_EVENT, eventDetails);
		return db.insert(DATABASE_TABLE_CURRENTTEAMEVENTS, null, initValues);

	}
	
	public Cursor getCurrentTeamEvents()
	{
			return db.query(DATABASE_TABLE_CURRENTTEAMEVENTS,
					null, null, null, null, null, null);
	}
	
	public boolean deleteMyEvent(String eventId) 
	{
		return db.delete(DATABASE_TABLE_MYEVENTS, KEY_ID_STRING_EVENT + 
				"=" + "'" + eventId + "'", null) > 0;
	} 
	
	public long insertImage(String imageName,String image){
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_IMAGE_NAME, imageName);
		initialValues.put(KEY_IMAGE, image);
		
		return db.insert(DATABASE_TABLE_IMAGES, null, initialValues);
		
	}
	
	public Cursor getImage(String imageName){
		return db.query(DATABASE_TABLE_IMAGES,
				new String[] {KEY_IMAGE}, 
				KEY_IMAGE_NAME + "=" + "'" + imageName + "'", 
				null, null, null, null);
	}


}
